﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using App.Account.DataAccess.Interfaces;
using App.Account.DataAccess.SqlServer.Models;

namespace App.Account.DataAccess.SqlServer
{
    public class SqlAccountRepository : IAccountRepository
    {
        public AccountRepositoryDataContext db;

        public SqlAccountRepository()
        {
            this.db = new AccountRepositoryDataContext(
                Properties.Settings.Default.Connection);
        }

        public IQueryable<App.Account.Models.User> GetUsers()
        {
            return from u in db.Users
                   select new App.Account.Models.User
                   {
                       Email = u.Email,
                       ID = u.UserID,
                       Name = u.Name,
                       Password = u.Password,
                       Roles = (from r in db.Roles
                                join ur in db.UsersRoles on r.RoleID equals ur.RoleID
                                where ur.UserID == u.UserID
                                select new App.Account.Models.Role
                                {
                                    ID = r.RoleID,
                                    Name = r.Name
                                }).ToList(),
                   };
        }

        public IQueryable<App.Account.Models.Role> GetRoles()
        {
            return from r in db.Roles
                   select new App.Account.Models.Role
                   {
                       ID = r.RoleID,
                       Name = r.Name,
                       Level = r.Level
                   };
        }

        public void SaveUser(App.Account.Models.User user)
        {
            User existingUser = (from u in db.Users
                                 where u.UserID == user.ID
                                 select u).SingleOrDefault();

            if (existingUser == null)
            {
                db.Users.InsertOnSubmit(new User
                {
                    UserID = user.ID,
                    Email = user.Email,
                    Password = user.Password,
                    Name = user.Name,
                });

                if(user.Roles!=null)
                    foreach (App.Account.Models.Role role in user.Roles)
                    {
                        db.UsersRoles.InsertOnSubmit(
                            new UsersRole
                            {
                                UsersRolesID = Guid.NewGuid(),
                                RoleID = role.ID,
                                UserID = user.ID
                            });
                    }
            }
            else
            {
                existingUser.Email = user.Email;
                existingUser.Password = user.Password;
                existingUser.Name = user.Name;



                List<UsersRole> existingUserRoles = (from ur in db.UsersRoles
                                                    where ur.UserID == user.ID
                                                    select ur).ToList();

                List<Guid> existingRoles = (from ur in existingUserRoles
                                            select ur.RoleID).ToList();

                List<Guid> updatedRoles = (from r in user.Roles
                                           select r.ID).ToList();

                foreach (UsersRole ur in existingUserRoles)
                {
                    if (!updatedRoles.Contains(ur.RoleID))
                        db.UsersRoles.DeleteOnSubmit(ur);
                }

                foreach (Guid roleID in updatedRoles)
                {
                    if (!existingRoles.Contains(roleID))
                        db.UsersRoles.InsertOnSubmit(
                            new UsersRole
                            {
                                UsersRolesID = Guid.NewGuid(),
                                RoleID = roleID,
                                UserID = user.ID
                            });
                }
            }

            db.SubmitChanges();
        }

        public void DeleteUser(App.Account.Models.User user)
        {
            User existingUser = (from u in db.Users
                                 where u.UserID == user.ID
                                 select u).SingleOrDefault();
            if (existingUser != null)
            {
                db.UsersRoles.DeleteAllOnSubmit((from ur in db.UsersRoles
                                                 where ur.UserID == existingUser.UserID
                                                 select ur).ToList());
                db.Users.DeleteOnSubmit(existingUser);
            }

            db.SubmitChanges();
        }

        public bool InsertUser(App.Account.Models.User user)
        {
            User existingUser = (from u in db.Users
                                 where u.UserID == user.ID || u.Name == user.Name
                                    || u.Email == user.Email
                                 select u).SingleOrDefault();

            if (existingUser == null)
            {
                db.Users.InsertOnSubmit(new User
                {
                    UserID = user.ID,
                    Email = user.Email,
                    Password = user.Password,
                    Name = user.Name,
                });

                foreach (App.Account.Models.Role role in user.Roles)
                {
                    db.UsersRoles.InsertOnSubmit(
                        new UsersRole
                        {
                            UsersRolesID = Guid.NewGuid(),
                            RoleID = role.ID,
                            UserID = user.ID
                        });
                }

                db.SubmitChanges();

                return true;
            }
            else
                return false;
        }



        public void SaveRole(App.Account.Models.Role role)
        {

            Role existingRole = (from u in db.Roles
                                 where u.RoleID == role.ID
                                 select u).SingleOrDefault();

            if (existingRole == null)
            {
                db.Roles.InsertOnSubmit(new Role
                {
                    Level = role.Level,
                    Name = role.Name,
                    RoleID = role.ID
                });


            }
            else
            {
                existingRole.Level = role.Level;
                existingRole.Name = role.Name;
            }

            db.SubmitChanges();
        }

        public void DeleteRole(App.Account.Models.Role role)
        {
            Role existingRole = (from u in db.Roles
                                 where u.RoleID == role.ID
                                 select u).SingleOrDefault();
            if (existingRole != null)
            {
                db.UsersRoles.DeleteAllOnSubmit((from ur in db.UsersRoles
                                                 where ur.RoleID == existingRole.RoleID
                                                 select ur).ToList());
                db.Roles.DeleteOnSubmit(existingRole);
            }

            db.SubmitChanges();
        }

        public bool InsertRole(App.Account.Models.Role role)
        {
            Role existingRole = (from u in db.Roles
                                 where u.RoleID == role.ID
                                 select u).SingleOrDefault();

            if (existingRole == null)
            {
                db.Roles.InsertOnSubmit(new Role
                {
                    Level = role.Level,
                    Name = role.Name,
                    RoleID = role.ID
                });

                db.SubmitChanges();

                return true;
            }
            else
                return false;
        }

    }
}
